﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Security;

namespace YAF.Classes.Data
{
    public static class DB
    {
        #region Basic Forum Properties

        /// <summary>
        /// Gets the database size
        /// </summary>
        /// <returns>intager value for database size</returns>
        public static int DBSize
        {
            get
            {
                using (SqlCommand cmd = new SqlCommand("select sum(cast(size as integer)) / 128 from sysfiles"))
                {
                    cmd.CommandType = CommandType.Text;
                    return (int)DBAccess.ExecuteScalar(cmd);
                }
            }
        }

        public static bool IsForumInstalled
        {
            get
            {
                try
                {
                    using (DataTable dt = board_list(DBNull.Value))
                    {
                        return dt.Rows.Count > 0;
                    }
                }
                catch
                { 
                }

                return false;
            }
        }

        public static int DBVersion
        {
            get
            {
                try
                {
                    using (DataTable dt = registry_list("version"))
                    {
                        if (dt.Rows.Count > 0)
                        {
                            // get the version...
                            return Convert.ToInt32(dt.Rows[0]["Value"]);
                        }
                    }
                }
                catch
                {
                    // not installed...
                }

                return -1;
            }
            
        }

        // MS SQL Support fulltext...
        private static bool _fullTextSupported = true;

        public static bool FullTextSupported
        {
            get
            {
                return _fullTextSupported;
            }
            set
            {
                _fullTextSupported = value;
            }
        }

        private static string _fullTextScript = "mssql/fulltext.sql";

        public static string FullTextScript
        {
            get
            {
                return _fullTextScript;
            }
            set
            {
                _fullTextScript = value;
            }
        }

        private static readonly string[] _scriptList = {
		                                               	"mssql/tables.sql",
		                                               	"mssql/indexes.sql",
		                                               	"mssql/constraints.sql",
		                                               	"mssql/triggers.sql",
		                                               	"mssql/views.sql",
		                                               	"mssql/procedures.sql",
		                                               	"mssql/functions.sql",
		                                               	"mssql/providers/procedures.sql",
		                                               	"mssql/providers/tables.sql",
		                                               	"mssql/providers/indexes.sql"
		                                               };

        static public string[] ScriptList
        {
            get
            {
                return _scriptList;
            }
        }

        #endregion

        #region Forum

        static public DataRow pageLoad(object sessionID, object boardID, object userKey, object ip, object location, object browser,
            object platform, object categoryID, object forumID, object topicID, object messageID, object donttrack)
        {
            int nTries = 0;
            while (true)
            {
                try
                {
                    using (SqlCommand cmd = DBAccess.GetCommand("pageload"))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("SessionID", sessionID);
                        cmd.Parameters.AddWithValue("BoardID", boardID);
                        cmd.Parameters.AddWithValue("UserKey", userKey);
                        cmd.Parameters.AddWithValue("IP", ip);
                        cmd.Parameters.AddWithValue("Location", location);
                        cmd.Parameters.AddWithValue("Browser", browser);
                        cmd.Parameters.AddWithValue("Platform", platform);
                        cmd.Parameters.AddWithValue("CategoryID", categoryID);
                        cmd.Parameters.AddWithValue("ForumID", forumID);
                        cmd.Parameters.AddWithValue("TopicID", topicID);
                        cmd.Parameters.AddWithValue("MessageID", messageID);
                        cmd.Parameters.AddWithValue("DontTrack", donttrack);
                        using (DataTable dt = DBAccess.GetData(cmd))
                        {
                            if (dt.Rows.Count > 0)
                                return dt.Rows[0];
                            else
                                return null;
                        }
                    }
                }
                catch (SqlException x)
                {
                    if (x.Number == 1025 && nTries < 3)
                    {
                        /// Transaction (Process ID XXX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
                    }
                    else
                        throw new ApplicationException(string.Format("Sql Exception with error number {0} (Tries={1})", x.Number, nTries), x);
                }
                ++nTries;
            }
        }
        /// <summary>
		/// Returns Search results
		/// </summary>
		/// <param name="ToSearch"></param>
		/// <param name="sf">Field to search</param>
		/// <param name="sw">Search what</param>
		/// <param name="fid"></param>
		/// <param name="UserID">ID of user</param>
		/// <returns>Results</returns>
        static public DataTable GetSearchResult(string toSearchWhat, string toSearchFromWho, SearchWhatFlags searchFromWhoMethod, SearchWhatFlags searchWhatMethod, int forumIDToStartAt, int userID, int boardId, int maxResults, bool useFullText)
        {
            bool bFirst = true;
            System.Text.StringBuilder forumIds = new System.Text.StringBuilder();

            if (toSearchWhat == "*")
            {
                toSearchWhat = "";
            }

            if (forumIDToStartAt != 0)
            {
                DataTable dt = forum_listall_sorted(boardId, userID, null, false, forumIDToStartAt);

                bFirst = true;
                foreach (DataRow dr in dt.Rows)
                {
                    if (bFirst) bFirst = false;
                    else
                        forumIds.Append(", ");

                    forumIds.Append(Convert.ToString(Convert.ToInt32(dr["ForumID"])));
                }
            }

            // fix quotes for SQL insertion...
            toSearchWhat = toSearchWhat.Replace("'", "''").Trim();
            toSearchFromWho = toSearchFromWho.Replace("'", "''").Trim();

            string searchSql = (maxResults == 0) ? "SELECT" : ("SELECT TOP " + maxResults.ToString());

            searchSql += " a.ForumID, a.TopicID, a.Topic, b.UserID, IsNull(c.Username, b.Name) as Name, c.MessageID, c.Posted, c.Message, c.Flags ";
            searchSql += "from {databaseOwner}.{objectQualifier}topic a left join {databaseOwner}.{objectQualifier}message c on a.TopicID = c.TopicID left join {databaseOwner}.{objectQualifier}user b on c.UserID = b.UserID join {databaseOwner}.{objectQualifier}vaccess x on x.ForumID=a.ForumID ";
            searchSql += String.Format("where x.ReadAccess<>0 AND x.UserID={0} AND c.IsApproved = 1 AND a.TopicMovedID IS NULL AND a.IsDeleted = 0 AND c.IsDeleted = 0 ", userID);

            string[] words;

            if (!String.IsNullOrEmpty(toSearchFromWho))
            {
                searchSql += "And (";
                bFirst = true;

                // generate user search sql...
                switch (searchFromWhoMethod)
                {
                    case SearchWhatFlags.AllWords:
                        words = toSearchFromWho.Replace("\"", "").Split(' ');
                        foreach (string word in words)
                        {
                            if (!bFirst) searchSql += " AND "; else bFirst = false;
                            searchSql += string.Format(" ((c.Username IS NULL AND b.Name LIKE N'%{0}%') OR (c.Username LIKE N'%{0}%'))", word);
                        }
                        break;
                    case SearchWhatFlags.AnyWords:
                        words = toSearchFromWho.Split(' ');
                        foreach (string word in words)
                        {
                            if (!bFirst) searchSql += " OR "; else bFirst = false;
                            searchSql += string.Format(" ((c.Username IS NULL AND b.Name LIKE N'%{0}%') OR (c.Username LIKE N'%{0}%'))", word);
                        }
                        break;
                    case SearchWhatFlags.ExactMatch:
                        searchSql += string.Format(" ((c.Username IS NULL AND b.Name = N'{0}') OR (c.Username = N'{0}'))", toSearchFromWho);
                        break;
                }
                searchSql += ")";
            }

            if (!String.IsNullOrEmpty(toSearchWhat))
            {
                searchSql += "AND (";
                bFirst = true;

                // generate message and topic search sql...'
                switch (searchWhatMethod)
                {
                    case SearchWhatFlags.AllWords:
                        words = toSearchWhat.Replace("\"", "").Split(' ');
                        if (useFullText)
                        {
                            string ftInner = "";

                            // make the inner FULLTEXT search
                            foreach (string word in words)
                            {
                                if (!bFirst) ftInner += " AND "; else bFirst = false;
                                ftInner += String.Format(@"""{0}""", word);
                            }
                            // make final string...
                            searchSql += string.Format("( CONTAINS (c.Message, N' {0} ') OR CONTAINS (a.Topic, N' {0} ') )", ftInner);
                        }
                        else
                        {
                            foreach (string word in words)
                            {
                                if (!bFirst) searchSql += " AND "; else bFirst = false;
                                searchSql += String.Format("(c.Message like N'%{0}%' OR a.Topic LIKE N'%{0}%')", word);
                            }
                        }
                        break;
                    case SearchWhatMethod.AnyWords:
                        words = toSearchWhat.Split(' ');

                        if (useFullText)
                        {
                            string ftInner = "";

                            // make the inner FULLTEXT search
                            foreach (string word in words)
                            {
                                if (!bFirst) ftInner += " OR "; else bFirst = false;
                                ftInner += String.Format(@"""{0}""", word);
                            }
                            // make final string...
                            searchSql += string.Format("( CONTAINS (c.Message, N' {0} ') OR CONTAINS (a.Topic, N' {0} ') )", ftInner);
                        }
                        else
                        {
                            foreach (string word in words)
                            {
                                if (!bFirst) searchSql += " OR "; else bFirst = false;
                                searchSql += String.Format("c.Message LIKE N'%{0}%' OR a.Topic LIKE N'%{0}%'", word);
                            }
                        }
                        break;
                    case SearchWhatMethod.ExactMatch:
                        if (useFullText)
                        {
                            searchSql += string.Format("(CONTAINS(c.Message, N'  \"{0}\" ') OR CONTAINS(a.Topic, N' \"{0}\" '))", toSearchWhat);
                        }
                        else
                        {
                            searchSql += string.Format("c.Message LIKE N'%{0}%' OR a.Topic LIKE N'%{0}%' ", toSearchWhat);
                        }
                        break;
                }
                searchSql += ") ";
            }

            // Ederon : 6/16/2007 - forum IDs start above 0, if forum id is 0, there is no forum filtering
            if (forumIDToStartAt > 0 && forumIds.Length > 0)
            {
                searchSql += string.Format("AND a.ForumID IN ({0})", forumIds.ToString());
            }

            searchSql += " ORDER BY c.Posted DESC ";

            using (SqlCommand cmd = DBAccess.GetCommand(searchSql, true))
            {
                return DBAccess.GetData(cmd);
            }
        }
        #endregion

        #region DataSets
        /// <summary>
        /// Gets a list of categories????
        /// </summary>
        /// <param name="boardID">BoardID</param>
        /// <returns>DataSet with categories</returns>
        static public DataSet ds_forumadmin(object boardID)
        {
            using (YafDBConnManager connMan = new YafDBConnManager())
            {
                using (DataSet ds = new DataSet())
                {
                    using (SqlTransaction trans = connMan.OpenDBConnection.BeginTransaction(DBAccess.IsolationLevel))
                    {
                        using (SqlDataAdapter da = new SqlDataAdapter(DBAccess.GetObjectName("category_list"), connMan.DBConnection))
                        {
                            da.SelectCommand.Transaction = trans;
                            da.SelectCommand.Parameters.AddWithValue("BoardID", boardID);
                            da.SelectCommand.CommandType = CommandType.StoredProcedure;
                            da.Fill(ds, DBAccess.GetObjectName("Category"));
                            da.SelectCommand.CommandText = DBAccess.GetObjectName("forum_list");
                            da.Fill(ds, DBAccess.GetObjectName("ForumUnsorted"));

                            DataTable dtForumListSorted = ds.Tables[DBAccess.GetObjectName("ForumUnsorted")].Clone();
                            dtForumListSorted.TableName = DBAccess.GetObjectName("Forum");
                            ds.Tables.Add(dtForumListSorted);
                            dtForumListSorted.Dispose();
                            forum_list_sort_basic(ds.Tables[DBAccess.GetObjectName("ForumUnsorted")], ds.Tables[DBAccess.GetObjectName("Forum")], 0, 0);
                            ds.Tables.Remove(DBAccess.GetObjectName("ForumUnsorted"));
                            ds.Relations.Add("FK_Forum_Category", s.Relations.Add( "FK_Forum_Category", ds.Tables[DBAccess.GetObjectName( "Category" )].Columns["CategoryID"], ds.Tables[DBAccess.GetObjectName( "Forum" )].Columns["CategoryID"] ));
                            trans.Commit();
                        }

                        return ds;
                    }
                }
            }
        }
        #endregion

        #region AccessMask
        /// <summary>
        /// Gets a list of access mask properities
        /// </summary>
        /// <param name="boardID">ID of Board</param>
        /// <param name="accessMaskID">ID of access mask</param>
        /// <returns></returns>
        static public DataTable accessmask_list(object boardID, object accessMaskID)
        {
            return accessmask_list(boardID, accessMaskID);
        }

        /// <summary>
        /// Gets a list of access mask properities
        /// </summary>
        /// <param name="boardID">ID of Board</param>
        /// <param name="accessMaskID">ID of access mask</param>
        /// <param name="excludeFlags">Ommit access masks with this flags set.</param>
        /// <returns></returns>
        static public DataTable accessmask_list(object boardID, object accessMaskID, object excludeFlags)
        {
            using (SqlCommand cmd = DBAccess.GetCommand("accessmask_list"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("BoardID", boardID);
                cmd.Parameters.AddWithValue("AccessMaskID", accessMaskID);
                cmd.Parameters.AddWithValue("ExcludeFlags", excludeFlags);
                return DBAccess.GetData(cmd);
            }
        }

        /// <summary>
        /// Deletes an access mask
        /// </summary>
        /// <param name="accessMaskID">ID of access mask</param>
        /// <returns></returns>
        static public bool accessmask_delete(object accessMaskID)
        {
            using (SqlCommand cmd = DBAccess.GetCommand("accessmask_delete"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("AccessMaskID", accessMaskID);
                return (int)DBAccess.ExecuteScalar(cmd) != 0;
            }
        }

        /// <summary>
        /// Saves changes to a access mask 
        /// </summary>
        /// <param name="accessMaskID">ID of access mask</param>
        /// <param name="boardID">ID of board</param>
        /// <param name="name">Name of access mask</param>
        /// <param name="readAccess">Read Access?</param>
        /// <param name="postAccess">Post Access?</param>
        /// <param name="replyAccess">Reply Access?</param>
        /// <param name="priorityAccess">Priority Access?</param>
        /// <param name="pollAccess">Poll Access?</param>
        /// <param name="voteAccess">Vote Access?</param>
        /// <param name="moderatorAccess">Moderator Access?</param>
        /// <param name="editAccess">Edit Access?</param>
        /// <param name="deleteAccess">Delete Access?</param>
        /// <param name="uploadAccess">Upload Access?</param>
        /// <param name="downloadAccess">Download Access?</param>
        static public void accessmask_save(object accessMaskID, object boardID, object name, object readAccess, object postAccess, object replyAccess, object priorityAccess, object pollAccess, object voteAccess, object moderatorAccess, object editAccess, object deleteAccess, object uploadAccess, object downloadAccess)
        {
            using (SqlCommand cmd = DBAccess.GetCommand("accessmask_save"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("AccessMaskID", accessMaskID);
                cmd.Parameters.AddWithValue("BoardID", boardID);
                cmd.Parameters.AddWithValue("Name", name);
                cmd.Parameters.AddWithValue("ReadAccess", readAccess);
                cmd.Parameters.AddWithValue("PostAccess", postAccess);
                cmd.Parameters.AddWithValue("ReplyAccess", replyAccess);
                cmd.Parameters.AddWithValue("PriorityAccess", priorityAccess);
                cmd.Parameters.AddWithValue("PollAccess", pollAccess);
                cmd.Parameters.AddWithValue("VoteAccess", voteAccess);
                cmd.Parameters.AddWithValue("ModeratorAccess", moderatorAccess);
                cmd.Parameters.AddWithValue("EditAccess", editAccess);
                cmd.Parameters.AddWithValue("DeleteAccess", deleteAccess);
                cmd.Parameters.AddWithValue("UploadAccess", uploadAccess);
                cmd.Parameters.AddWithValue("DownloadAccess", downloadAccess);
                DBAccess.ExecuteNonQuery(cmd);
            }
        }
        #endregion

        #region Active
        /// <summary>
        /// Gets list of active users
        /// </summary>
        /// <param name="boardID">BoardID</param>
        /// <param name="Guests"></param>
        /// <returns>Returns a DataTable of active users</returns>
        static public DataTable active_list(object boardID, object Guests)
        {
            using (SqlCommand cmd = DBAccess.GetCommand("active_list"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("BoardID", boardID);
                cmd.Parameters.AddWithValue("Guests", Guests);
                return DBAccess.GetData(cmd);
            }
        }

        /// <summary>
        /// Gets the list of active users within a certain forum
        /// </summary>
        /// <param name="forumID">forumID</param>
        /// <returns>DataTable of all ative users in a forum</returns>
        static public DataTable active_listforum(object forumID)
        {
            using (SqlCommand cmd = DBAccess.GetCommand("active_listforum"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("ForumID", forumID);
                return DBAccess.GetData(cmd);
            }
        }
        /// <summary>
        /// Gets the list of active users in a topic
        /// </summary>
        /// <param name="topicID">ID of topic </param>
        /// <returns>DataTable of all users that are in a topic</returns>
        static public DataTable active_listtopic(object topicID)
        {
            using (SqlCommand cmd = DBAccess.GetCommand("active_listtopic"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("TopicID", topicID);
                return DBAccess.GetData(cmd);
            }
        }

        /// <summary>
        /// Gets the activity statistics for a board
        /// </summary>
        /// <param name="boardID">boardID</param>
        /// <returns>DataRow of activity stata</returns>
        static public DataRow active_stats(object boardID)
        {
            using (SqlCommand cmd = DBAccess.GetCommand("active_stats"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("BoardID", boardID);
                using (DataTable dt = DBAccess.GetData(cmd))
                {
                    return dt.Rows[0];
                }
            }
        }
        #endregion

        #region Attachment

        /// <summary>
        /// Gets a list of attachments
        /// </summary>
        /// <param name="messageID">messageID</param>
        /// <param name="attachmentID">attachementID</param>
        /// <param name="boardID">boardID</param>
        /// <returns>DataTable with attachement list</returns>
        static public DataTable attachment_list(object messageID, object attachmentID, object boardID)
        {
            using (SqlCommand cmd = DBAccess.GetCommand("attachment_list"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("MessageID", messageID);
                cmd.Parameters.AddWithValue("AttachmentID", attachmentID);
                cmd.Parameters.AddWithValue("BoardID", boardID);
                return DBAccess.GetData(cmd);
            }
        }
        /// <summary>
        /// saves attachment
        /// </summary>
        /// <param name="messageID">messageID</param>
        /// <param name="fileName">File Name</param>
        /// <param name="bytes">number of bytes</param>
        /// <param name="contentType">type of attchment</param>
        /// <param name="stream">stream of bytes</param>
        static public void attachment_save(object messageID, object fileName, object bytes, object contentType, System.IO.Stream stream)
        {
            using (SqlCommand cmd = DBAccess.GetCommand("attachment_save"))
            {
                byte[] fileData = null;
                if (stream != null)
                {
                    fileData = new byte[stream.Length];
                    stream.Seek(0, System.IO.SeekOrigin.Begin);
                    stream.Read(fileData, 0, (int)stream.Length);
                }
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("MessageID", messageID);
                cmd.Parameters.AddWithValue("FileName", fileName);
                cmd.Parameters.AddWithValue("Bytes", bytes);
                cmd.Parameters.AddWithValue("ContentType", contentType);
                cmd.Parameters.AddWithValue("FileData", fileData);
                DBAccess.ExecuteNonQuery(cmd);
            }
        }
        //ABOT CHANGE 16.04.04
        /// <summary>
        /// Delete attachment
        /// </summary>
        /// <param name="attachmentID">ID of attachment to delete</param>
        static public void attachment_delete(object attachmentID)
        {
            bool UseFileTable = false;

            using(DataTable dt = YAF.Classes.Data.DB.registry_list("UseFileTable"))
                foreach ( DataRow dr in dt.Rows )
                    UseFileTable = Convert.ToBoolean(Convert.ToInt32(dr["Value"]));

            //If the files are actually saved in the Hard Drive
            if (!UseFileTable)
            {
                using (SqlCommand cmd = DBAccess.GetCommand("attachment_list"))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("AttachmentID", attachmentID);
                    DataTable tbAttachments = DBAccess.GetData(cmd);
                    string sUpDir = HttpContext.Current.Server.MapPath(YAF.Classes.Config.UploadDir);
                    foreach (DataRow row in tbAttachments.Rows)
                        System.IO.File.Delete(String.Format("{0}{1}.{2}", sUpDir, row["MessageID"], row["FileName"]));
                }
            }
            using (SqlCommand cmd = DBAccess.GetCommand("attachment_delete"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("AttachmentID", attachmentID);
                DBAccess.ExecuteNonQuery(cmd);
            }
            //End ABOT CHANGE 16.04.04
        }

        /// <summary>
        /// Attachement dowload
        /// </summary>
        /// <param name="attachmentID">ID of attachemnt to download</param>
        static public void attachment_download(object attachmentID)
        {
            using (SqlCommand cmd = DBAccess.GetCommand("attachment_download"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("AttachmentID", attachmentID);
                DBAccess.ExecuteNonQuery(cmd);
            }
        }
        #endregion

        #region BannedIP
        /// <summary>
        /// List of Baned IP's
        /// </summary>
        /// <param name="boardID">ID of board</param>
        /// <param name="ID">ID</param>
        /// <returns>DataTable of banned IPs</returns>
        static public DataTable bannedip_list(object boardID, object ID)
        {
            using (SqlCommand cmd = DBAccess.GetCommand("bannedip_list"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("BoardID", boardID);
                cmd.Parameters.AddWithValue("ID", ID);
                return DBAccess.GetData(cmd);
            }
        }

        /// <summary>
        /// Saves baned ip in database
        /// </summary>
        /// <param name="ID">ID</param>
        /// <param name="boardID">BoardID</param>
        /// <param name="Mask">Mask</param>
        static public void bannedip_save(object ID, object boardID, object Mask)
        {
            using (SqlCommand cmd = DBAccess.GetCommand("bannedip_save"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("ID", ID);
                cmd.Parameters.AddWithValue("BoardID", boardID);
                cmd.Parameters.AddWithValue("Mask", Mask);
                DBAccess.ExetuteNonQuery(cmd);
            }
        }

        /// <summary>
        /// Deletes Banned IP
        /// </summary>
        /// <param name="ID">ID of banned ip to delete</param>
        static public void bannedip_delete(object ID)
        {
            using (SqlCommand cmd = DBAccess.GetCommand("bannedip_delete"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("ID", ID);
                DBAccess.ExecuteNonQuery(cmd);
            }
        }
        #endregion

        #region Board
        /// <summary>
        /// Gets a list of information about a board
        /// </summary>
        /// <param name="boardID">board id</param>
        /// <returns>DataTable</returns>
        static public DataTable board_list(object boardID)
        {
            using (SqlCommand cmd = DBAccess.GetCommand("board_list"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("BoardID", boardID);
                return DBAccess.GetData(cmd);
            }
        }
        /// <summary>
        /// Gets posting statistics
        /// </summary>
        /// <param name="boardID">BoardID</param>
        /// <returns>DataRow of Poststats</returns>
        static public DataRow board_poststats(object boardID)
        {
            using (SqlCommand cmd = DBAccess.GetCommand("board_poststats"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("BoardID", boardID);
                using (DataTable dt = DBAccess.GetData(cmd))
                {
                    return dt.Rows[0];
                }
            }
        }
        /// <summary>
        /// Recalculates topic and post numbers and updates last post for all forums in all boards
        /// </summary>
        static public void board_resync()
        {
            board_resync(null);
        }
        /// <summary>
        /// Recalculates topic and post numbers and updates last post for specified board
        /// </summary>
        /// <param name="boardID">BoardID of board to do re-sync for, if null, all boards are re-synced</param>
        static public void board_resync(object boardID)
        {
            using (SqlCommand cmd = DBAccess.GetCommand("board_resync"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("BoardID", boardID);
                DBAccess.ExecuteNonQuery(cmd);
            }
        }
        /// <summary>
        /// Gets statistica about number of posts etc.
        /// </summary>
        /// <returns>DataRow</returns>
        static public DataRow board_stats()
        {
            return board_stats(null);
        }
        static public DataRow board_stats(object boardID)
        {
            using (SqlCommand cmd = DBAccess.GetCommand("board_stats"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("BoardID", boardID);

                using (DataTable dt = DBAccess.GetData(cmd))
                {
                    return dt.Rows[0];
                }
            }
        }
        /// <summary>
        /// Saves board information
        /// </summary>
        /// <param name="boardID">BoardID</param>
        /// <param name="name">Name of Board</param>
        /// <param name="allowThreaded">Boolen value, allowThreaded</param>
        static public void board_save(object boardID, object name, object allowThreaded)
        {
            using (SqlCommand cmd = DBAccess.GetCommand("board_save"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("BoardID", boardID);
                cmd.Parameters.AddWithValue("Name", name);
                cmd.Parameters.AddWithValue("AllowThreaded", allowThreaded);
                DBAccess.ExecuteNonQuery(cmd);
            }
        }
        /// <summary>
        /// Creates a new board
        /// </summary>
        /// <param name="adminUsername">Membership Provider User Name</param>
        /// <param name="adminUserKey">Membership Provider User Key</param>
        /// <param name="boardName">Name of new board</param>
        /// <param name="boardMembershipName">Membership Provider Application Name for new board</param>
        /// <param name="boardRolesName">Roles Provider Application Name for new board</param>
        static public void board_create(object adminUsername, object adminUserKey, object boardName, object boardMembershipName, object boardRolesName)
        {
            using (SqlCommand cmd = DBAccess.GetCommand("board_create"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("BoardName", boardName);
                cmd.Parameters.AddWithValue("MembershipAppName", boardMembershipName);
                cmd.Parameters.AddWithValue("RolesAppName", boardRolesName);
                cmd.Parameters.AddWithValue("UserName", adminUsername);
                cmd.Parameters.AddWithValue("UserKey", adminUserKey);
                cmd.Parameters.AddWithValue("IsHostAdmin", 0);
                DBAccess.ExecuteNonQuery(cmd);
            }
        }
        /// <summary>
        /// Deletes a board
        /// </summary>
        /// <param name="boardID">ID of board to delete</param>
        static public void board_delete(object boardID)
        {
            using (SqlCommand cmd = DBAccess.GetCommand("board_delete"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("BoardID", boardID);
                DBAccess.ExecuteNonQuery(cmd);
            }
        }
        #endregion

        #region Category
        /// <summary>
        /// Deletes a category
        /// </summary>
        /// <param name="CategoryID">ID of category to delete</param>
        /// <returns>Bool value indicationg if category was deleted</returns>
        static public bool category_delete(object CategoryID)
        {
            using (SqlCommand cmd = DBAccess.GetCommand("category_delete"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("CategoryID", CategoryID);
                return (int)DBAccess.ExecuteScalar(cmd) != 0;
            }
        }

        /// <summary>
        /// Gets a list of forums in a category
        /// </summary>
        /// <param name="boardID">boardID</param>
        /// <param name="categoryID">categotyID</param>
        /// <returns>DataTable with a list of forums in a category</returns>
        static public DataTable category_list(object boardID, object categoryID)
        {
            using (SqlCommand cmd = DBAccess.GetCommand("category_list"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("BoardID", boardID);
                cmd.Parameters.AddWithValue("CategoryID", categoryID);
                return DBAccess.GetData(cmd);
            }
        }
        /// <summary>
        /// Gets a list of forum categories
        /// </summary>
        /// <param name="boardID"></param>
        /// <param name="userID"></param>
        /// <param name="categoryID"></param>
        /// <returns></returns>
        static public DataTable category_listread(object boardID, object userID, object categoryID)
        {
            using (SqlCommand cmd = DBAccess.GetCommand("category_listread"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("BoardID", boardID);
                cmd.Parameters.AddWithValue("UserID", userID);
                cmd.Parameters.AddWithValue("CategoryID", categoryID);
                return DBAccess.GetData(cmd);
            }
        }
        /// <summary>
        /// Lists categories very simply (for URL rewriting)
        /// </summary>
        /// <param name="StartID"></param>
        /// <param name="Limit"></param>
        /// <returns></returns>
        static public DataTable category_simplelist(int startID, int limit)
        {
            using (SqlCommand cmd = DBAccess.GetCommand("category_simplelist"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("StartID", startID);
                cmd.Parameters.AddWithValue("Limit", limit);
                return DBAccess.GetData(cmd);
            }
        }
        /// <summary>
        /// Saves changes to a category
        /// </summary>
        /// <param name="boardID">BoardID</param>
        /// <param name="CategoryID">CategoryID so save changes to</param>
        /// <param name="Name">Name of the category</param>
        /// <param name="SortOrder">Sort Order</param>
        
        #endregion
    }
}
